# _*_ coding:utf-8 _*_
# @Time  : 2022.07.16
# @Author: zizlee
import datetime
import sys
import MySQLdb
from MySQLdb.converters import conversions
from MySQLdb.constants import FIELD_TYPE

from db import FAConnection


def mysqlclient_converters():
    conversions[FIELD_TYPE.BIT] = lambda x: 1 if int.from_bytes(x, byteorder=sys.byteorder, signed=False) else 0
    return conversions


params = {
    'port': 3306,
    'user': 'root',
    'password': 'mysql',
    'database': 'worker_recorder'
}


work_db = MySQLdb.connect(charset='utf8', conv=mysqlclient_converters(), **params)
cursor = work_db.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT a.*,b.username FROM work_onduty_message As a INNER JOIN user_user As b ON a.author_id=b.id;")
records = cursor.fetchall()

# 查询用户
ruizy_db = FAConnection()

user_list = ruizy_db.query('SELECT admin_name,id FROM ruizy_user WHERE is_analyst=1;', keep_conn=True)
user_id_dict = {u['admin_name']: u['id'] for u in user_list}


# 处理数据，处理ID
save_list = []
# 过滤掉傅彬彬的记录
records = list(filter(lambda x: x['username'] != '傅彬彬', records))
for r in records:
    new_user_id = user_id_dict.get(r['username'])
    if not new_user_id:
        raise ValueError('new_user_id not exists!')
    r['user_id'] = new_user_id
    r['create_date'] = datetime.datetime.fromtimestamp(r['create_time']).strftime('%Y%m%d')
    save_list.append(r)

cursor.close()
work_db.close()


# 保存数据
insert_sql = """
    INSERT INTO work_onduty_message (create_date,user_id,content,note,is_active)
    VALUE (%(create_date)s,%(user_id)s,%(content)s,%(note)s,%(is_active)s);
"""
print(len(save_list))
c = ruizy_db.insert(insert_sql, param=save_list, many=True)
print('迁移成功!', c[0])
